package com.hangxingliu.exceltools;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Boolean;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelAPI {

	public static class PreviewInfo {
		public String[][] data;
		public String[] title;
		public int row, col;
		public boolean allData;
		public PreviewInfo(String[][] data, String[] title,int row, int col,boolean allData) {
			this.data = data;
			this.title = title;
			this.row = row; 
			this.col = col;
			this.allData = allData;
		}
	}

	public static class DuplicationInfo {
		public String sheetName;
		public int startRow, column;
		public HashMap<String, ArrayList<String>> duplication;
	}

	public boolean success = true;
	public String errorInformation;

	public Workbook excel = null;
	public String excelPath;
	public String[] sheetNames;

	public boolean isClosed = false;

	public ExcelAPI(String excelPath) { this.excelPath = excelPath; } 

	public boolean close() {
		isClosed = true;
		try{
			excel.close();
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public PreviewInfo preview(String sheetName, int maxRow) {
		Sheet sheet = excel.getSheet(sheetName);
		int col = sheet.getColumns();
		int row = sheet.getRows();
		boolean all = true;

		if(maxRow <= 0) maxRow = row;
		if(maxRow < row) {
			row = maxRow;
			all = false;
		}
		
		String[][] dat = new String[all ? row : (row+1)][col];
		String[] title = new String[col];
		Cell[] cs;
		for(int y = 0 ; y < row ; y++){
			cs = sheet.getRow(y);
			for(int x = 0 ; x < cs.length ; x++ ){
				dat[y][x] = cs[x].getContents();	
			}
		}
		for(int x = 0 ; x < col ; x ++ ) {
			title[x] = Utils.columnInt2Str(x);
			if(!all)
				dat[row][x] = "...";
		}
		return new ExcelAPI.PreviewInfo(dat, title, row, col, all);
	}

	public DuplicationInfo findDuplication(String sheetName, int startRow, int column){
		Sheet sheet = excel.getSheet(sheetName);
		HashMap<String, String> mapExist = new HashMap<String, String>();
		HashMap<String, ArrayList<String>> duplication = new HashMap<String,ArrayList<String>>();
		
		Cell[] cells = sheet.getColumn(column);
		String columnStr = Utils.columnInt2Str(column);
		String name = null;
		String existed, location;
		for(int i = startRow; i < cells.length ; i++ ){
			name = cells[i].getContents().trim().replace(" ", "").replace("\t", "");
			if(name.length() == 0) continue;

			existed = mapExist.get(name);
			location = String.format("%s%d", columnStr, i + 1 );
			
			if(existed != null){	
				ArrayList<String> list = duplication.get(name);
				if(list == null) {
					list = new ArrayList<String>();
					list.add(existed);
					list.add(location);
					duplication.put(name, list);
				} else {
					list.add(location);
				}
			}else{
				mapExist.put(name, location);
			}
		}

		DuplicationInfo result = new ExcelAPI.DuplicationInfo();
		result.duplication = duplication;
		result.sheetName = sheetName;
		result.startRow = startRow;
		result.column = column;
		return result;
	}

	public void exportDuplicatedItems(DuplicationInfo info, String exportFilePath) throws Exception {
		File f = new File(exportFilePath);
		if(f.exists() && !f.delete())
			throw new Exception("无法删除旧的重复结果文件!");

		WritableWorkbook book = Workbook.createWorkbook(f);
		WritableSheet wsheet = book.createSheet("重复结果", 0);

		Sheet sheet = excel.getSheet(info.sheetName);
		Iterator<Map.Entry<String, ArrayList<String>>> it = info.duplication.entrySet().iterator();

		Cell cells[];
		int insertRow = 0;

		HashSet<Integer> cols = new HashSet<Integer>();
		HashSet<Integer> rows = new HashSet<Integer>();
		while(it.hasNext()) {
			Map.Entry<String, ArrayList<String>> item = it.next();
			ArrayList<String> locations = item.getValue();
			for (String loc : locations) {
				int r = sheet.getCell(loc).getRow();
				rows.add(r);

				cells = sheet.getRow(r);
				for(Cell cell: cells) {
					int c = cell.getColumn();
					cols.add(c);
					wsheet.addCell(getWritableCell(insertRow, c, cell));
				}
				insertRow++;
			}
		}

		for(Integer col: cols)
			wsheet.setColumnView(col, new CellView(sheet.getColumnView(col)));
		for(Integer row: rows)
			wsheet.setRowView(row, new CellView(sheet.getRowView(row)));
	

		book.write();
		book.close();
	}

	private WritableCell getWritableCell(int row, int col, Cell cell) {
		CellType type = cell.getType();
		WritableCell wcell = null;
		if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA) {
			wcell = new Number(col, row, ((NumberCell)cell).getValue() );
		} else if(type == CellType.DATE || type == CellType.DATE_FORMULA) {
			wcell = new DateTime(col, row, ((DateCell)cell).getDate());
		} else if(type == CellType.BOOLEAN || type == CellType.BOOLEAN_FORMULA) {
			wcell = new Boolean(col, row, ((BooleanCell)cell).getValue());
		} else {
			wcell = new Label(col, row, cell.getContents());
		}
		wcell.setCellFormat(new WritableCellFormat(cell.getCellFormat()));
		return wcell;
	}

	public static ExcelAPI Open(String filePath) {
		ExcelAPI result = new ExcelAPI(filePath);
		try{
			Workbook excel = Workbook.getWorkbook(new File(filePath));
			result.excel = excel;
			result.sheetNames = excel.getSheetNames();
		} catch(Exception e) {
			e.printStackTrace();
			result.success = false;
			result.errorInformation = e.getMessage();
		}
		return result;
	}
}
